import sqlite3
import os.path
import config
from time import time

def struct_decl(structure, column=None):
    """
    returns a string that can be used for column definitions based on
    the structure<dict> object passed
    
    i.e.:
            {
              "id": {
                     "def": "integer primary key autoincrement"
                     },
              "name": {
                      "def": "text not null collate nocase"
                      },
              "aid": {
                      "def": "integer default 0"
                      },
              "url": {
                      "def": "text not null collate nocase"
                      },
              }
    will return:
        '(url text not null collate nocase, aid integer default 0, id integer
         primary key autoincrement, name text not null collate nocase)'
         
    the column argument can be given to only include specific columns, can be
    a string or list object
    """
    if (column == None):
        return "(" + ", ".join([x + " " + y['def'] for x, y in structure.iteritems()]) + ")"
    else:
        column = column if isinstance(column, list) else [column]
        return "(" + ", ".join([x + " " + y['def'] for x, y in structure.iteritems() if x in column]) + ")"

def initialize_database(name, table_structure):
    with SQLiteCursor(name) as cur:
        for table, structure in table_structure.iteritems():
            cur.execute("SELECT count(*) AS exist FROM sqlite_master WHERE \
            type='table' AND name=?;", (table,))
            if (cur.fetchone()['exist'] == 0):
                #create table
                cur.execute(u"CREATE TABLE {table} {declar};"\
                            .format(table=table, declar=struct_decl(structure)))
            else:
                # check table
                # TODO check for new columns that have to be made
                cur.execute("PRAGMA table_info({table});".format(table=table))
                for row in cur:
                    column = row['name']
                    # check for changes
                    for key, value in structure[column].iteritems():
                        if (key in row.keys()):
                            if (row[key] != value):
                                # Fix shit here
                                column_def = structure[column].get("def", None)
                                if (column_def):
                                    cur.execute("ALTER TABLE {table} ALTER COLUMN ? ?;"\
                                                .format(table=table),
                                                (column, column_def))
                # check for deletion
                cur.execute("PRAGMA table_info({table});".format(table=table))
                for column in [x["name"] for x in cur if x["name"] not in structure.iterkeys()]:
                    # TODO: Can't drop tables, have to change query
                    #cur.execute("ALTER TABLE {table} DROP COLUMN {column};"\
                    #            .format(table=table, column=column1))
                    print("Wanted to be deleted: " + column)
class SQLiteCursor(object):
    _databases = {}
    _timeouts = {}
    _references = {}
    def __init__(self, name, conn=None):
        object.__init__(self)
        self.name = name
        self._references[name] = 0
        self._timeouts[name] = time()
        if (conn != None):
            self.conn = conn
        else:
            if (name in self._databases):
                self.conn = self._databases[name]
            else:
                self._databases[name] = sqlite3.connect(os.path.join(config.data_dir("sites"), name + ".db"))
                self.conn = self._databases[name]
        self.conn.row_factory = sqlite3.Row
        self.cursor = self.conn.cursor()
        for key, value in self._timeouts.iteritems():
            if ((time() - value) > 600) and (self._references[key] <= 0):
                self._databases[key].close()
                del self._timeouts[key]
                del self._references[key]
    def __enter__(self):
        self._references[self.name] += 1
        return self.cursor
    def __exit__(self, type, value, traceback):
        self._references[self.name] -= 1
        self.cursor.close()
        self.conn.commit()